package excel

import (
	"encoding/csv"
	"errors"
	"fmt"
	"github.com/fatih/color"
	"github.com/goravel/framework/facades"
	"github.com/goravel/framework/support/carbon"
	"github.com/goravel/framework/support/path"

	"github.com/spf13/cast"
	"github.com/xuri/excelize/v2"
	"goravel/app/models"
	"io"
	"os"
	"path/filepath"
)

type Excelservice struct{}

func NewExcelService() *Excelservice {
	return &Excelservice{}
}

type LoadFile struct {
	Name string `json:"name"`
	Type string `json:"type"`
}

// 获取路径下的所有文件，提取文件名，返回LoadFile数组
func (*Excelservice) GetFiles() ([]LoadFile, error) {
	//获取到public目录
	dir, _ := os.Getwd()
	//判断如果是windows系统，就把\替换成/,public目录下的excel目录为excel文件夹
	if os.IsPathSeparator('\\') {
		dir = dir + "\\public\\excel\\"
	} else {
		dir = dir + "/public/excel/"
	}
	dirPth := dir //获取当前目录
	files := make([]LoadFile, 1)
	dir, err := filepath.Abs(dirPth)
	if err != nil {
		return nil, err
	}
	filepath.Walk(dir, func(filename string, fi os.FileInfo, err error) error {
		if err != nil {
			return err
		}
		if fi.IsDir() {
			return nil
		}
		//后缀名
		ext := filepath.Ext(fi.Name())
		files = append(files, LoadFile{
			Name: fi.Name(),
			Type: ext, //文件后缀名
		})
		return nil
	})
	//从files中提取文件名，删除后缀
	for i, file := range files {

		if filepath.Ext(file.Type) == ".xlsx" {
			files[i].Name = file.Name[0 : len(file.Name)-5]
		}
		if filepath.Ext(file.Type) == ".xls" {
			files[i].Name = file.Name[0 : len(file.Name)-4]
		}
		if filepath.Ext(file.Type) == ".csv" {
			files[i].Name = file.Name[0 : len(file.Name)-4]
		}
	}
	//过滤掉files中的file中的Name为空的元素
	var files2 []LoadFile
	for _, file := range files {
		if file.Name != "" {
			files2 = append(files2, file)
		}
	}
	//判断files2是否为空
	if len(files2) == 0 {
		err33 := errors.New("无文件，请放入指定文件")
		return nil, err33
	}
	return files2, nil
}

func (*Excelservice) ReadStreamExcel() error {
	loadFiles, err0 := NewExcelService().GetFiles()
	if err0 != nil {
		return err0
	}

	dir, _ := os.Getwd()
	cols := make([]string, 0)
	//1、先读取表头，也就是.xlsx或者.xls文件的第一行

	for _, loadFile := range loadFiles {
		if filepath.Ext(loadFile.Type) == ".xlsx" || filepath.Ext(loadFile.Type) == ".xls" {
			//判断操作系统，调整路径
			header, err := NewExcelService().GetHeader(loadFile.Name, loadFile.Type)
			if err != nil {
				return err
			} else {
				cols = header
			}
			//创建表
			error22 := NewExcelService().CrtTable(loadFile.Name, cols)
			if error22 != nil {
				return error22
			}
		}
	}
	//	2、再读取内容，也就是.csv文件的所有内容
	for _, loadFile := range loadFiles {
		if filepath.Ext(loadFile.Type) == ".csv" {

			//判断操作系统
			var dirPath string
			if os.IsPathSeparator('\\') {
				dirPath = dir + "\\public\\excel\\" + loadFile.Name + loadFile.Type
			} else {
				dirPath = dir + "/public/excel/" + loadFile.Name + loadFile.Type
			}
			//流式读取csv
			csvFile, err := os.Open(dirPath)
			if err != nil {
				return err
			}
			defer csvFile.Close()
			//提示信息
			fmt.Println("开始读取csv文件..." + loadFile.Name + loadFile.Type)
			color.Set(color.FgMagenta, color.Bold)
			//判断有多少个sheet，循环读取
			reader := csv.NewReader(csvFile)

			for i := 0; ; i++ {
				if row, err := reader.Read(); err == nil {
					NewExcelService().InstCol(loadFile.Name, cols, row)
					//打印进度/|\- 提示正在运行,/|\-是转圈的动画
					fmt.Printf("\r正在导入数据...%v", []string{"/", "|", "\\", "-"}[i%4])
				} else {
					if err == io.EOF {
						break
					}
					return err
				}
			}
			//当读取完毕后，打印读取完毕提示
			fmt.Println("\n🏆数据导入完毕！表名：" + loadFile.Name)

			color.Unset() // Don't forget to unset
		}
	}
	return nil
}

// CrtTable 创建数据库表,第一参数为表名，第二个参数[]string为表头
func (*Excelservice) CrtTable(tablename string, cols []string) error {
	crtSql := "create table " + tablename + "("
	//crtSql += "id bigint identity(1,1) primary key, "
	for _, col := range cols {
		crtSql += "\"" + col + "\"" + " varchar(255) DEFAULT NULL" + ","
	}
	crtSql = crtSql[0:len(crtSql)-1] + ")"
	//日志打印
	//facades.Log.Info(crtSql)
	//执行sql语句
	_, err := facades.Orm().Query().Exec(crtSql)
	if err != nil {
		return err
	}
	return nil
}

// InstCol 插入数据:创建数据库表,第一参数为表名，第二个参数[]string为表头,第三个参数为数据
func (*Excelservice) InstCol(tablename string, cols []string, vals []string) {
	instSql := "INSERT INTO " + tablename + " ("
	for _, col := range cols {
		instSql += "\"" + col + "\"" + ","
	}
	instSql = instSql[0:len(instSql)-1] + ") VALUES ("
	for _, val := range vals {
		instSql += "N'" + val + "',"
	}
	instSql = instSql[0:len(instSql)-1] + ");\n"

	facades.Orm().Query().Exec(instSql)
}

func (*Excelservice) GetHeader(name string, filetype string) ([]string, error) {
	var dirPath string
	dir, _ := os.Getwd()
	cols := make([]string, 0)

	if os.IsPathSeparator('\\') {
		dirPath = dir + "\\public\\excel\\" + name + filetype
	} else {
		dirPath = dir + "/public/excel/" + name + filetype
	}
	file, err1 := os.Open(dirPath)
	if err1 != nil {
		return nil, err1
	}
	//流式读取excel
	f, err2 := excelize.OpenReader(file)
	if err2 != nil {
		return nil, err2
	}
	//读取表的第一行作为表头，在创建该表
	rows, _ := f.GetRows("Sheet1")
	for _, row := range rows {
		for _, colCell := range row {
			cols = append(cols, colCell)
		}
		break
	}
	return cols, nil
}

func (e *Excelservice) LoadFile(att_id uint, name string) error {
	filepath := fmt.Sprintf("%s/%s", path.Public("excel"), name)
	//读取文件内容
	file, err := os.Open(filepath)
	if err != nil {
		return err
	}
	f, err := excelize.OpenReader(file)
	defer f.Close()
	if err != nil {
		return err
	}
	//获取第一个读取配置
	readCmdSheet := f.GetSheetName(0)
	r_rows, err := f.GetRows(readCmdSheet)
	if err != nil {
		return err
	}
	readTrancateSql := fmt.Sprintf("truncate table readcmds")
	facades.Orm().Query().Exec(readTrancateSql)
	//获取表头
	for _, row := range r_rows {
		//从第三行开始读取，同时如果该行为合并了单元格，跳过
		var readcmd models.Readcmd
		readcmd.DataAddress = row[0]
		readcmd.ByteSize = cast.ToInt(row[1])
		readcmd.Parameter = row[2]
		//判断row有没有第3项，如果有就直接赋值，如果没有就直接赋值为空字符
		if len(row) > 3 {
			readcmd.Unit = row[3]
		} else {
			readcmd.Unit = ""
		}
		//判断row有没有第4项，如果有就直接赋值，如果没有就直接赋值为空字符
		if len(row) > 4 {
			readcmd.Note = row[4]
		} else {
			readcmd.Note = ""
		}

		if err := facades.Orm().Query().Model(&models.Readcmd{}).Create(&readcmd); err != nil {
			fmt.Sprintf("导入数据失败，请检查数据格式！%s", err.Error())
			return err
		}
	}
	return nil
}

func (e *Excelservice) LoadTemplate(att_id uint, name string) error {
	filepath := fmt.Sprintf("%s/%s", path.Public("excel"), name)
	//读取文件内容
	file, err := os.Open(filepath)
	if err != nil {
		return err
	}
	f, err := excelize.OpenReader(file)
	defer f.Close()
	if err != nil {
		return err
	}
	//获取第一个读取配置
	readCmdSheet := f.GetSheetName(0)
	r_rows, err := f.GetRows(readCmdSheet)
	if err != nil {
		return err
	}
	//readTrancateSql := fmt.Sprintf("truncate table readcmds")
	//facades.Orm().Query().Exec(readTrancateSql)
	//获取表头
	db := facades.Orm().Query()
	const (
		ROWLENGTH = 11
	)
	for index, row := range r_rows {
		//从第二行开始读取，同时判断当row[1]没有值时，直接跳过本行

		if index == 0 {
			continue
		}
		row_len := len(row)
		//1-如果发现row的长度小于11时，给他增加到11
		if row_len < ROWLENGTH {
			for i := 0; i < ROWLENGTH-row_len; i++ {
				row = append(row, "")
			}
		}
		if row[1] == "" {
			continue
		}
		device := models.Device{
			UserID:        cast.ToUint(row[0]),
			Code:          row[1],
			Name:          row[2],
			BoardModel:    row[3],
			OnlineAt:      carbon.NewDateTime(carbon.Now()),
			LocationAt:    carbon.NewDateTime(carbon.Now()),
			Address:       row[6],
			BatteryStatus: row[7],
			Spec:          row[8],
			TowerCode:     row[9],
			SerialNumber:  row[10],
		}
		db.Model(&models.Device{}).UpdateOrCreate(&device, models.Device{Code: device.Code}, models.Device{
			UserID:        device.UserID,
			Name:          device.Name,
			BoardModel:    device.BoardModel,
			Address:       device.Address,
			BatteryStatus: device.BatteryStatus,
			Spec:          device.Spec,
			TowerCode:     device.TowerCode,
			SerialNumber:  device.SerialNumber,
		})
	}
	return nil
}
